-- ** 创建者   : suning
-- ** 创建日期 : 20221030
-- ** 功能描述 ：动静对比-全链路判责-判责
-- **************************************************************************
-- **************************** 修改日志 ************************************
-- **************************************************************************
-- ** 依赖表：
-- **************************************************************************
-- alter table add columns 20230216 新增
--     ,actual_effect                         bigint          comment'动态时效'
--     ,scan_site_planned_departure_time      timestamp       comment'当前站点发件任务号计划发车时间'
--     ,scan_site_planned_arrival_time        timestamp       comment'当前站点发件任务号计划到车时间'
--     ,scan_site_if_stop                     bigint          comment'当前站点是否经停'
-- alter table add columns 20230515 新增
--  ,if_taitc                              string          comment'问题件抬头错标识'
--  ,order_source_code                     string          comment'订单来源编码'
--  ,order_source_name                     string          comment'订单来源名称'
--  ,if_reback_reach                       bigint          comment'判责自退转拦截件是否达成'
--  ,reback_apply_time                     timestamp       comment'退转件申请时间'
--  ,intercept_registration_time           timestamp       comment'拦截件登记时间'
--  ,cainiao_plan_sign_time                timestamp       comment'自有平台规划签收日期'
--  ,ziyou_plan_sign_time                  timestamp       comment'自有平台规划签收日期'
--  ,start_center_code                     string          comment'始发中心编码'
--  ,start_center_name                     string          comment'始发中心名称'
--  ,end_center_code                       string          comment'目的中心编码'
--  ,end_center_name                       string          comment'目的中心名称'
--  ,scan_site_plan_send_time              timestamp       comment'当前站点规划发车时间'
--  ,scan_site_plan_arrival_time           timestamp       comment'当前站点规划到车时间'
--  ,scan_site_if_static                   bigint          comment'当前站点是否静态'
--  ,next_site_if_static                   bigint          comment'下一站点是否静态'
-- alter table add columns 20231130 新增
--  ,is_main_route                         bigint          comment'路由类型'
-- *******************  CURRENT_VERSION ： V.1.1  ***************************
drop table  jms_dm.dm_whole_decide_duty_dt;
create external table if not exists jms_dm.dm_whole_decide_duty_dt(
     waybill_no                            string          comment'运单号'
    ,platform_effect                       bigint          comment'自有平台时效标准(兜底路由)'
    ,ziyou_platform_effect                 bigint          comment'自有平台时效标准(推荐路由)'
    ,if_platform_standard                  bigint          comment'自有平台时效是否达成:1达成,0未达成(推荐路由)'
    ,if_doudi_platform_standard            bigint          comment'自有平台时效是否达成:1达成,0未达成(兜底路由)'
    ,cainiao_platform_name                 string          comment'七星潭平台名称'
    ,cainiao_platform_effect               bigint          comment'七星潭平台时效标准'
    ,if_cainiao_platform_pres_standard     bigint          comment'七星潭平台时效是否达成:1达成,0未达成'
    ,first_duty_type                       string          comment'首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更'
    ,first_ziyou_duty_type                 string          comment'自有平台静态责任'
    ,if_intercept                          bigint          comment'是否拦截件:1是,0否'
    ,if_reback                             bigint          comment'是否退回件:1是,0否'
    ,if_transfer                           bigint          comment'是否转寄件:1是,0否'
    ,start_city_code                       string          comment'始发城市编码'
    ,start_city_name                       string          comment'始发城市名称'
    ,sign_city_code                        string          comment'签收城市编码'
    ,sign_city_name                        string          comment'签收城市名称'
    ,taking_time                           timestamp       comment'揽收时间'
    ,deliver_time                          timestamp       comment'出仓时间'
    ,aging_sign_time                       timestamp       comment'时效签收时间'
    ,actual_sign_time                      timestamp       comment'实际签收时间'
    ,economic_circle_name                  string          comment'经济圈名称'
    ,scan_site_no                          string          comment'当前站点顺序号'
    ,scan_site_code                        string          comment'当前站点编码'
    ,scan_site_name                        string          comment'当前站点名称'
    ,scan_site_type                        string          comment'当前站点类型'
    ,scan_site_arrival_time                timestamp       comment'当前站点到件时间'
    ,scan_site_send_time                   timestamp       comment'当前站点发件时间'
    ,scan_site_actual_arrival_time         timestamp       comment'当前站点实际到车时间'
    ,scan_site_actual_send_time            timestamp       comment'当前站点实际发车时间'
    ,scan_site_seal_time                   string          comment'当前站点封车时间'
    ,scan_site_actual_shipment_no          string          comment'当前站点实际发车任务号'
    ,scan_site_actual_shipment_name        string          comment'当前站点实际发车任务名'
    ,scan_site_arrival_bagg_code           string          comment'当前站点到件包号'
    ,scan_site_arrival_bagg_name           string          comment'当前站点到件包名'
    ,scan_site_send_bagg_code              string          comment'当前站点发件包号'
    ,scan_site_send_bagg_name              string          comment'当前站点发件包名'
    ,scan_site_send_bagg_addr_code         string          comment'当前站发件建包目的地编码'
    ,scan_site_send_bagg_addr_name         string          comment'当前站发件建包目的地名称'
    ,scan_site_arrival_bagg_addr_code      string          comment'当前站到件建包目的地编码'
    ,scan_site_arrival_bagg_addr_name      string          comment'当前站到件建包目的地名称'
    ,next_site_code                        string          comment'当前站点实际下一站编码'
    ,next_site_name                        string          comment'当前站点实际下一站名称'
    ,next_site_type                        string          comment'当前站点实际下一站类型'
    ,actual_diff_time                      string          comment'当前站点与下一站实际时间差(小时)'
    ,actual_node                           string          comment'实际节点'
    ,first_network_code                    string          comment'始发网点编码'
    ,first_network_name                    string          comment'始发网点名称'
    ,sign_network_code                     string          comment'签收网点编码'
    ,sign_network_name                     string          comment'签收网点名称'
    ,last_network_code                     string          comment'目的网点编码'
    ,last_network_name                     string          comment'目的网点名称'
    ,plan_last_taking_time                 string          comment'始发网点规划最晚揽收时间'
    ,plan_sign_time                        timestamp       comment'目的网点规划签收时间'
    ,total_days                            bigint          comment'T+时效'
    ,route_id                              string          comment'路由唯一键'
    ,if_taking_off                         string          comment'是否截单后揽收'
    ,duty_type                             string          comment'当前站点与下一站点责任类型'
    ,actual_effect                         bigint          comment'动态时效'
    ,scan_site_planned_departure_time      timestamp       comment'当前站点发件任务号计划发车时间'
    ,scan_site_planned_arrival_time        timestamp       comment'当前站点发件任务号计划到车时间'
    ,scan_site_if_stop                     bigint          comment'当前站点是否经停'
    ,if_taitc                              string          comment'问题件抬头错标识'
    ,order_source_code                     string          comment'订单来源编码'
    ,order_source_name                     string          comment'订单来源名称'
    ,if_reback_reach                       bigint          comment'判责自退转拦截件是否达成'
    ,reback_apply_time                     timestamp       comment'退转件申请时间'
    ,intercept_registration_time           timestamp       comment'拦截件登记时间'
    ,cainiao_plan_sign_time                timestamp       comment'自有平台规划签收日期'
    ,ziyou_plan_sign_time                  timestamp       comment'自有平台规划签收日期'
    ,start_center_code                     string          comment'始发中心编码'
    ,start_center_name                     string          comment'始发中心名称'
    ,end_center_code                       string          comment'目的中心编码'
    ,end_center_name                       string          comment'目的中心名称'
    ,scan_site_plan_send_time              timestamp       comment'当前站点规划发车时间'
    ,scan_site_plan_arrival_time           timestamp       comment'当前站点规划到车时间'
    ,scan_site_if_static                   bigint          comment'当前站点是否静态'
    ,next_site_if_static                   bigint          comment'下一站点是否静态'
) comment '经营指标-全链路判责'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_whole_decide_duty_dt'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);
